﻿CREATE proc [dbo].[sp_getSumData] (@beginDate Datetime='1970-01-01',@endDate Datetime='1970-01-01')
as 
/*
* @beginDate 开始日期
* @endDate 结束日期
*/
begin 
	--是否获取全部数据
	declare @IsGetAllData as bit = 0    
	--都为默认值，返回所有数据
	if (@beginDate = @endDate and convert(varchar,@beginDate,23) = '1970-01-01' )
	set @IsGetAllData = 1 

	if (@IsGetAllData = 1)
	begin 
		select t1.reportCnt,0 as sumReportCnt,
			t2.apptCnt,0 as sumApptCnt,
			t3.regCnt,0 as sumRegCnt,
			t4.payCnt,0 as sumPayCnt,
			t5.bindCnt,0 as sumBindCnt,
			t6.userCnt,0 as sumUserCnt,
			t2.apptFee + t3.regFee + t4.payFee as transFee , 0.0 as sumTransFee,
			t2.apptCnt+ t3.regCnt + t4.payCnt as transCnt ,0 as sumTransCnt,
			case when payCnt = 0 then 0.0 else ((t2.apptCnt+ t3.regCnt)/payCnt) * 1.0 end  as apptPayRate,
			0.0 as sumApptPayRate,
			t7.avgActiveCnt,0 as sumAvgActiveCnt,
			t8.docCnt ,0 as sumDocCnt
		from 
		(
			-- 报告查看数
			select a.examCnt + b.labCnt as reportCnt from 
			(select COUNT(*) as examCnt from MedExam) a,  	   	   	   	   
			(select COUNT(*) as labCnt from MedLab) b	
		) t1 ,
		(
			--预约数
			select COUNT(*) as apptCnt, sum(coalesce(SumFee,0.0)) as apptFee from ApptOrder where OrderType = 0 and OrderStatus in (21,23)
		) t2 ,
		(
			--挂号数（含取号）
			select COUNT(*) as regCnt,SUM(coalesce(SumFee,0.0)) as regFee from ApptOrder where OrderType in (1,2) and OrderStatus = 21 
		) t3,
		(
			--缴费数
			select COUNT(*) as payCnt,SUM(coalesce(PrescMoney,0.0) -  coalesce(YibaoMoney,0.0)) as payFee from BillOrder where OrderStatus = 8 
		) t4,
		(
			--绑卡数
			select COUNT(*) as bindCnt from HospCard where RecStatus = 1 
		) t5,
		(
			--用户数
			select COUNT(*) as userCnt from WxUser where RecStatus = 1 		   
		) t6,
		(
			--日活动用户数			
			select COUNT(*) as  avgActiveCnt from WxUser 		
		) t7,
		(
			--医生数
			select count(distinct DoctorId) as docCnt  from Doctor where RecStatus = 1 
		) t8

	end  
	--开始结束日期段内数据
	else   	  	
	begin 
		select t1.reportCnt,t1.sumReportCnt,
			t2.apptCnt,t2.sumApptCnt, 
			t3.regCnt,t3.sumRegCnt,
			t4.payCnt,t4.sumPayCnt,
			t5.bindCnt,t5.sumBindCnt,
			t6.userCnt,t6.sumUserCnt,
			t2.apptFee + t3.regFee + t4.payFee as transFee , t2.sumApptFee + t3.sumRegFee + t4.sumPayFee as sumTransFee ,
			t2.apptCnt+ t3.regCnt + t4.payCnt as transCnt , t2.sumApptCnt+ t3.sumRegCnt + t4.sumPayCnt as sumTransCnt ,
			case when t4.payCnt =0 then 0.0 else ((t2.apptCnt+ t3.regCnt)/t4.payCnt) *1.0 end as apptPayRate,
			case when t4.sumPayCnt= 0 then 0.0 else ((t2.sumApptCnt+ t3.sumRegCnt)/t4.sumPayCnt) * 1.0 end as sumApptPayRate,
			t7.avgActiveCnt,t7.sumAvgActiveCnt,
			t8.docCnt,t8.sumDocCnt 
		from 
		(
			-- 报告查看数
			select a.examCnt + b.labCnt as reportCnt,c.sumExamCnt + d.sumLabCnt as sumReportCnt from 
			(select COUNT(*) as examCnt from MedExam where ExamDate between @beginDate and @endDate) a,  	   	   	   	   
			(select COUNT(*) as labCnt from MedLab where LabDate between @beginDate and @endDate) b,
			(select COUNT(*) as sumExamCnt from MedExam where ExamDate <= @endDate) c,
			(select COUNT(*) as sumLabCnt from MedLab where LabDate <= @endDate) d
		) t1 ,
		(		   
			--预约数
			select a.apptCnt,a.apptFee,b.sumApptCnt,b.sumApptFee from 
			(select COUNT(*) as apptCnt, sum(coalesce(SumFee,0.0)) as apptFee from ApptOrder 
			where OrderType = 0 and OrderStatus in (21,23) and FetchTime between @beginDate and @endDate ) a,
			(select COUNT(*) as sumApptCnt, sum(coalesce(SumFee,0.0)) as sumApptFee from ApptOrder 
			where OrderType = 0 and OrderStatus in (21,23) and FetchTime  <= @endDate ) b
		) t2 ,
		(
			--挂号数（含取号）
			select a.regCnt,a.regFee,b.sumRegCnt,b.sumRegFee from 
			(select COUNT(*) as regCnt,SUM(coalesce(SumFee,0.0)) as regFee from ApptOrder 
			where OrderType in (1,2) and OrderStatus = 21  and FetchTime between @beginDate and @endDate ) a,			   
			( select COUNT(*) as sumRegCnt,SUM(coalesce(SumFee,0.0)) as sumRegFee from ApptOrder 
			where OrderType in (1,2) and OrderStatus = 21  and FetchTime  <= @endDate ) b
		) t3,
		(
			--缴费数
			select a.payCnt,a.payFee,b.sumPayCnt,b.sumPayFee from 
			(select COUNT(*) as payCnt,SUM(coalesce(PrescMoney,0.0) - coalesce(YibaoMoney,0.0)) as payFee from BillOrder 
			where OrderStatus = 8 and FetchTime  between @beginDate and @endDate ) a,
			(select COUNT(*) as sumPayCnt,SUM(coalesce(PrescMoney,0.0) - coalesce(YibaoMoney,0.0)) as sumPayFee from BillOrder 
			where OrderStatus = 8 and  FetchTime  <= @endDate ) b
		) t4,
		(
			--绑卡数
			select a.bindCnt,b.sumBindCnt from 
			(select COUNT(*) as bindCnt from HospCard 
			where RecStatus = 1 and RecCreateDt  between @beginDate and @endDate ) a,
			(select COUNT(*) as sumBindCnt from HospCard 
			where RecStatus = 1 and RecCreateDt  <= @endDate ) b
		) t5,
		(
			--用户数
			select a.userCnt,b.sumUserCnt from 			   
			(select COUNT(*) as userCnt from WxUser 
			where RecStatus = 1 and RecCreateDt between @beginDate and @endDate) a,
			(select COUNT(*) as sumUserCnt from WxUser 
			where RecStatus = 1 and RecCreateDt  <= @endDate) b 	 	   
		) t6,
		(
			--日活动用户数
			select a.avgActiveCnt,b.sumAvgActiveCnt from
			(select COUNT(*) avgActiveCnt from WxUser 
			where LastActiveDt between @beginDate and @endDate)a,
			(select COUNT(*) sumAvgActiveCnt from WxUser
			where LastActiveDt <= @endDate ) b			
		) t7,
		(			
			--医生数
			select a.docCnt ,b.sumDocCnt from (
			select count(distinct DoctorId) as docCnt  from Doctor
			where RecStatus = 1 and RecCreateDt between @beginDate and @endDate ) a,
			(select count(distinct DoctorId) as sumDocCnt  from Doctor 
			where  RecStatus = 1  and RecCreateDt <= @endDate  ) b
		) t8			
	end 			   
end 

